package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import orm.ChildrenBehaviorPurpose;
import orm.CollectivityWeekArrange;
import orm.CollectivityWeekTimeBucket;
import orm.TeachingPlanBaseInfo;
import orm.TopicTransferModel;
import util.DBHelper;

public class CWeekArrangeDAO {
	
	public void insertCWeekkArrange(Statement statement, 
			ArrayList<CollectivityWeekArrange> cwaArr, String cWeekArrangeID) throws SQLException {
		CollectivityWeekArrange cwa = null;
		for (int i = 0; i < cwaArr.size(); i++) {
			cwa = cwaArr.get(i);
			statement.execute("insert into collectivityweekarrange values('" + cWeekArrangeID + "'," +
					cwa.getWeekID() + ", '" + cwa.getActivitySchedules() + "', '" + cwa.getRemark() + "')");
		}
	}
	
	public void updateCWeekkArrange(Statement statement,
			ArrayList<CollectivityWeekArrange> cwaArr, String cWeekArrangeID) throws SQLException {
		CollectivityWeekArrange cwa = null;
		for (int i = 0; i < cwaArr.size(); i++) {
			cwa = cwaArr.get(i);

			StringBuffer sqlStr = new StringBuffer();
			sqlStr.append("update collectivityweekarrange set ActivitySchedules = '" );
			sqlStr.append(cwa.getActivitySchedules() + "', Remark = '" + cwa.getRemark());
			sqlStr.append("' where CWeekArrangeID = '" + cwa.getCWeekArrangeID() + "' and WeekID = " + cwa.getWeekID() + "");
			statement.execute(sqlStr.toString());
		}
	}
	
	public void deleteCWeekkArrange(Statement statement, String cWeekArrangeID) throws SQLException {
		statement.execute("delete from collectivityweekarrange where CWeekArrangeID='" + cWeekArrangeID + "'");
	}
	
	public ArrayList<CollectivityWeekArrange> getCWAByCWAID(String cWeekArrangeID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		ArrayList<CollectivityWeekArrange> al = new ArrayList<CollectivityWeekArrange>();
		CollectivityWeekArrange cwa = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from collectivityweekarrange where CWeekArrangeID='" + cWeekArrangeID + "'");
		
		while (rs.next()) {
			cwa = new CollectivityWeekArrange();
			int i = 1;
			cwa.setCWeekArrangeID(rs.getString(i++));
			cwa.setWeekID(rs.getInt(i++));
			cwa.setActivitySchedules(rs.getString(i++));
			cwa.setRemark(rs.getString(i++));
			al.add(cwa);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
	public ArrayList<String> getTPIDSSByCWeekArrangeID(String cWeekArrangeID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select cwa.ActivitySchedules from collectivityweekarrange cwa where cwa.ActivitySchedules<>'' and cwa.CWeekArrangeID='" + cWeekArrangeID + "' union all ");
		sqlStr.append("select cwtb1.ThemeActivity   from collectivityweektimebucket cwtb1 where cwtb1.ThemeActivity<>'' and cwtb1.CWeekArrangeID='" + cWeekArrangeID + "' union all ");
		sqlStr.append("select cwtb2.ActivityArea    from collectivityweektimebucket cwtb2 where cwtb2.ActivityArea<>'' and cwtb2.CWeekArrangeID='" + cWeekArrangeID + "' union all ");
		sqlStr.append("select cwtb3.OpenAir         from collectivityweektimebucket cwtb3 where cwtb3.OpenAir<>'' and cwtb3.CWeekArrangeID='" + cWeekArrangeID + "'");
		
		rs = stmt.executeQuery(sqlStr.toString());
		
		ArrayList<String> al = new ArrayList<String>();
	
		while (rs.next()) {
			al.add(rs.getString(1));
		}
		rs.close();
		stmt.close();
		conn.close();
		
		ArrayList<String> tpids = new ArrayList<String>();
		for(String s:al){
			if(s.indexOf(",")==-1){
				tpids.add(s);
			}else{
				for(String temp : s.split(",")){
					tpids.add(temp);
				}
			}
		}
		
		return tpids;
	}
	public List<TopicTransferModel> queryTopicModels(List<String> topicIDs) throws SQLException{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		List<TopicTransferModel> result = new ArrayList<TopicTransferModel>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select tpbi.ActivityName, ctp.DevelopmentDomain from collectivityteachingplan ctp left join teachingplanbaseinfo tpbi on tpbi.TPID=ctp.TPID where 1<>1");
		
		for(String s : topicIDs){
			sqlStr.append(" or tpbi.TPID='").append(s).append("'");
		}
		
		rs = stmt.executeQuery(sqlStr.toString());
		
	
		while (rs.next()) {
			result.add(new TopicTransferModel(rs.getString(1),rs.getInt(2)));
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return result;
	}
	
}
